import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.hibernate.mapping.Property;
import vo.export.Info_vo;

import com.creawor.imei.base.Common_qry_QueryMap;

import export.ExportHandle;


public class QueryServlet extends HttpServlet {

	/**
	 * Constructor of the object.
	 */
	private String connUrl="jdbc:sqlserver://localhost:1433;DatabaseName=hz_market";
	private String connUser="sa";
	private String connPwd="123456";
	private String connDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	
//	private String connUrl="jdbc:mysql://127.0.0.1/mic";
//	private String connUser="root";
//	private String connPwd="1234";
//	private String connDriver="com.mysql.jdbc.Driver";
	
	private String filename;

	private String filepath;
	
	
	
	private Statement stmt	= null;
	private Integer	connKey	= 0;
	private Connection	conn = null;
	public QueryServlet() {
		super();
	}

	/**
	 * Destruction of the servlet. <br>
	 */
	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	/**
	 * The doGet method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request, response);
		
	}

	/**
	 * The doPost method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to post.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String year=request.getParameter("year");
		ArrayList al=getInfo(year);
		filename = request.getParameter("filename");
		filepath = request.getParameter("filepath");
		File fileWrite = new File(filepath+filename);
		fileWrite.createNewFile();
		OutputStream os = new FileOutputStream(fileWrite);
		ExportHandle exh=new ExportHandle();
		exh.writeExcel(os, ExportHandle.INFO,"宏观信息", al);
		request.setAttribute("list", al);
		request.getRequestDispatcher("/Info.jsp").forward(request, response);
	}
	
	/**
	 * get connection and Statement
	 * 
	 * @return
	 */
	private boolean _getConnection()
	{
		try
		{
			Class.forName(connDriver);
			if( conn == null || conn.isClosed() )
			{
				conn = DriverManager.getConnection(connUrl, connUser, connPwd);
				stmt = conn.createStatement();
			}

			if( stmt == null )
			{
				stmt = conn.createStatement();
			}
		}
		catch( SQLException e )
		{
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}

	private ArrayList getInfo(String year )
	{
		ArrayList al=new ArrayList();
		
		synchronized( connKey )
		{

			// check connection
//			if( !_getConnection() )
//			{
//				return null;
//			}

			// finding property
			ResultSet rs = null;
			Property p = null;
			String sql = "select county_name,population_num,gdp,gdp_average,key_industry,physiognomy from t_county where year = '"+year+"'";
			try
			{
				Common_qry_QueryMap query_map0 = new Common_qry_QueryMap();
//				rs = stmt.executeQuery(sql);
				ArrayList sl = query_map0.findAll(sql);
				 for(Iterator it = sl.iterator();it!=null&&it.hasNext();)
				 {
				 	Map map = (Map)it.next();
//				while(rs.next()){
					Info_vo vo=new Info_vo();
					vo.setDilhj(""+map.get("physiognomy"));
					vo.setDiq(""+map.get("county_name"));
					vo.setGDP(""+map.get("gdp"));
					vo.setRenjGDP(""+map.get("gdp_average"));
					vo.setRenk(""+map.get("population_num"));
					vo.setZhizcy(""+map.get("key_industry"));
					al.add(vo);
				}
//				rs.close();
				return al;
			}
			catch( Exception e )
			{
				e.printStackTrace();
			}
			finally
			{
//				if( rs != null )
//				{
//					try
//					{
//						rs.close();
//					}
//					catch( SQLException e )
//					{
//						e.printStackTrace();
//					}
//				}
			}
			return al;
		}
	}
	
	/**
	 * Initialization of the servlet. <br>
	 *
	 * @throws ServletException if an error occure
	 */
	public void init() throws ServletException {
		// Put your code here
	}

}
